-- This procedure takes a table name and an ID type (identity) key value in as
-- arguments and copies the row with that ID to a new row with a new ID.  The
-- new ID is returned.

CREATE  PROCEDURE CopyIDRow @TableName varchar(40), @KeyValue numeric AS
  DECLARE GetColumns CURSOR LOCAL READ_ONLY FOR
  SELECT b.name, b.autoval
    FROM sysobjects a, syscolumns b
   WHERE a.id = b.id
     AND a.name = @TableName
   ORDER By b.autoval
  BEGIN
    DECLARE
    @AutoVal varbinary,
    @ColumnName varchar(50),
    @KeyColumn varchar(50),
    @ColumnList varchar(2000),
    @InsertQuery nvarchar(4000)
    
    SET @ColumnList = ''
    OPEN GetColumns
     
    FETCH NEXT FROM GetColumns
     INTO @ColumnName, @AutoVal
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
         IF @AutoVal IS NULL
           BEGIN
             SET @ColumnList = @ColumnList + @ColumnName + ','
           END
         ELSE
           BEGIN
             SET @KeyColumn = @ColumnName
           END
      FETCH NEXT FROM GetColumns
       INTO @ColumnName, @AutoVal
      END
      
    CLOSE GetColumns
    DEALLOCATE GetColumns
    
    SET @ColumnList = Left(@ColumnList,Len(@ColumnList) - 1)
     
    SET @InsertQuery = 'INSERT INTO ' + @TableName + '(' + @ColumnList + ')' + 'SELECT ' + @ColumnList + ' FROM ' + @TableName + ' WHERE ' + @KeyColumn + ' = ' + CAST(@KeyValue AS varchar)
    
    EXECUTE sp_executesql @InsertQuery
  END

GO
GRANT EXECUTE ON  [dbo].[CopyIDRow] TO [IMIS]
GO